using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    #region public utility functions

    [SqlFunction]
    public static string GetStringFromList(SqlString listString, SqlString separator, SqlInt32 position)
    {
        char charSeparator = separator.ToString()[0];
        string[] ref_parts = listString.ToString().Split(charSeparator);
        string dlcRef = ref_parts[(int)position];

        return dlcRef;
    }

    [SqlFunction(IsDeterministic = true)]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString GetRegexFind([SqlFacet(MaxSize = -1)]SqlString expressionToFind, [SqlFacet(MaxSize = -1)]SqlString expressionToSearch, SqlInt32 instanceToReturn)
    {
        string return_value = string.Empty;

        Regex expressionObj = new Regex(expressionToFind.ToString(), RegexOptions.IgnoreCase);
        MatchCollection matches = expressionObj.Matches(expressionToSearch.ToString());

        int instance = (int)instanceToReturn;

        if (matches.Count != 0 && matches.Count >= instance)
        {
            return_value = matches[instance].Value;
        }

        return new SqlString(return_value);
    }

    [SqlFunction]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString GetRegexReplace([SqlFacet(MaxSize = -1)]SqlString expressionToFind, [SqlFacet(MaxSize = -1)]SqlString expressionToSearch, [SqlFacet(MaxSize = -1)]SqlString stringReplacement)
    {
        string return_value = string.Empty;

        Regex expressionObj = new Regex(expressionToFind.ToString(), RegexOptions.IgnoreCase);
        return_value = expressionObj.Replace(expressionToSearch.ToString(), stringReplacement.ToString());

        return new SqlString(return_value);
    }

    [SqlFunction]
    public static int GetStringInstanceCount(SqlString findString, SqlString searchString)
    {
        MatchCollection match_results = Regex.Matches(searchString.ToString(), findString.ToString());
        int instance_count = match_results.Count;
        return instance_count;
    }

    #endregion
}
